package com.estore.service.persitence;

import com.estore.service.model.Staff;
import com.estore.service.persitence.core.MySQLDatabaseConnector;
import com.estore.service.util.exception.EStoreException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StaffDatabaseManager {

    private Connection conn;
    private ResultSet result;

    public boolean insertStaff(Staff staff) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement insertStaff = null;
        try {
            insertStaff = conn.prepareStatement("insert into staff set "
                    + "firstname=?, surname=?, dateemployed=STR_TO_DATE(?,'%d/%m/%Y'), middlename=?,"
                    + " code=?, staffnumber=?, departmentid=?, status=?");
            insertStaff.setString(1, staff.getFirstName());
            insertStaff.setString(2, staff.getSurname());
            insertStaff.setString(3, staff.getDateEmployed());
            insertStaff.setString(4, staff.getMiddleName());
            insertStaff.setString(5, staff.getCode());
            insertStaff.setString(6, staff.getStaffNumber());
            insertStaff.setInt(7, staff.getDepartmentId());
            insertStaff.setString(8, staff.getStatus());
            insertStaff.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
            inserted = false;
        }
        return inserted;
    }

    public boolean updateStaff(Staff staff) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement updateStaff = null;
        try {
            updateStaff = conn.prepareStatement("update staff set "
                    + " firstname=?, surname=?, dateemployed=STR_TO_DATE(?,'%Y-%m-%d'), middlename=?,"
                    + " code=?, staffnumber=?, deparmentid=?, status=? where id=?");
             updateStaff.setString(1, staff.getFirstName());
            updateStaff.setString(2, staff.getSurname());
            updateStaff.setString(3, staff.getDateEmployed());
            updateStaff.setString(4, staff.getMiddleName());
            updateStaff.setString(5, staff.getCode());
            updateStaff.setString(6, staff.getStaffNumber());
            updateStaff.setInt(7, staff.getDepartmentId());
            updateStaff.setString(8, staff.getStatus());
            updateStaff.setInt(9, staff.getId());
            updateStaff.executeUpdate();
        } catch (SQLException e) {            
            inserted = false;
           throw new EStoreException(e);
        }
        return inserted;
    }

   

    public Staff getStaffById(int staffId) {
        Staff staff = new Staff();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectStaffById = null;
        try {
            selectStaffById = conn.prepareStatement("select * from staff where id=?");
            selectStaffById.setInt(1, staffId);
            result = selectStaffById.executeQuery();
            while (result.next()) {
                staff = getStaff(result);
            }
        } catch (SQLException e) {
            throw new EStoreException(e);
        }
        return staff;
    }

    public boolean updateStaffStatus(int staffId,String newStatus) {
        boolean updated=true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement updateStaffStatus = null;
        try {
            updateStaffStatus = conn.prepareStatement("update staff set status=? where id=?");            
            updateStaffStatus.setString(1, newStatus);
            updateStaffStatus.setInt(2, staffId);
            updateStaffStatus.executeUpdate();
        } catch (SQLException e) {
            updated=false;
            throw new EStoreException(e);
        }
        return updated;
    }

    public Staff getStaff(ResultSet result) {
        Staff staff = new Staff();
        try {
            staff = new Staff();
            staff.setFirstName(result.getString("firstname"));
            staff.setSurname(result.getString("surname"));
            staff.setDateEmployed(result.getString("dateemployed"));
            staff.setMiddleName(result.getString("middlename"));
            staff.setCode(result.getString("code"));
            staff.setStatus(result.getString("status"));
            staff.setStaffNumber(result.getString("staffnumber"));
            staff.setDepartmentId(result.getInt("departmentid"));
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return staff;
    }

    public List<Staff> getAllStaff(int start, int size) {
        List<Staff> staffs = new ArrayList<Staff>();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectAllStaff = null;
        try {
            selectAllStaff = conn.prepareStatement("select * from staff LIMIT ?,?");
            selectAllStaff.setInt(1, start);
            selectAllStaff.setInt(2, size);
            result = selectAllStaff.executeQuery();
            while (result.next()) {
                staffs.add(getStaff(result));
            }
        } catch (SQLException e) {
            throw new EStoreException(e);
        }
        return staffs;
    }

    public int getAllStaffCount() {
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectAllStaffCount = null;
        try {
            selectAllStaffCount = conn.prepareStatement("select count(*) from staff");
            result = selectAllStaffCount.executeQuery();
            while (result.next()) {
                return result.getInt(1);
            }
        } catch (SQLException e) {
            throw new EStoreException(e);
        }
        return 0;

    }
}
